import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def getAllOcp():
    print('return all occupation')
    sql = "SELECT * FROM occupation"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有岗位")
        return jsonify(0)
    else:
        print("查询成功")
        return temp


def newocp(name, duty):
    print('insert a new occupation')

    number = getnewnumber()
    sql = "insert into occupation values( '" + number + "', '" + name + "','" + duty + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getnewnumber():
    sql = "select max(occupa_id) from occupation"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str


def editocp(id, name, duty):
    print('edit staff')

    sql = "update occupation set occupa_name='" + name + "',occupa_duty='" + duty + "' where occupa_id='" + id + "'"
    print(sql)

    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def deleteOcp(word):
    sql = "Delete FROM occupation WHERE occupa_id =" + str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchOcp(word):
    sql = "SELECT * FROM occupation WHERE occupa_name LIKE '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的岗位")
        return 0
    else:
        print("搜索成功")
        return temp
